﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class D_Dangky:AccessData
    {
        //-----Hàm lấy ra hết------        
        public DataTable GetAll()
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Get_List";
            sqlCmd.Parameters.Clear();
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }

        //-----Hàm lấy theo PK DangkyID------
        public M_Dangky GetbyPK_DangkyID(int DangkyID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_GetByDangkyId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@DangkyID", DangkyID);
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                M_Dangky M_Dangky = null;
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        M_Dangky = new M_Dangky();

                        M_Dangky.DangkyID = Convert.ToInt32(dr["DangkyID"]);
                        M_Dangky.KhachID = Convert.ToInt32(dr["KhachID"]);
                        M_Dangky.PhongID = Convert.ToInt32(dr["PhongID"]);
                        if (dr["Giaphong"] != DBNull.Value)
                            M_Dangky.Giaphong = Convert.ToDecimal(dr["Giaphong"]);
                        if (dr["Ngaydangky"] != DBNull.Value)
                            M_Dangky.Ngaydangky = Convert.ToDateTime(dr["Ngaydangky"]);
                        if (dr["Ngaytraphong"] != DBNull.Value)
                            M_Dangky.Ngaytraphong = Convert.ToDateTime(dr["Ngaytraphong"]);
                        if (dr["Songay"] != DBNull.Value)
                            M_Dangky.Songay = Convert.ToInt32(dr["Songay"]);
                    }
                }
                dr.Close();
                sqlCmd.Connection.Close();
                return M_Dangky;
            }
        }

        //-----Hàm lấy theo FK------        
        public DataTable GetByFK_KhachID(int KhachID)
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_GetByKhachId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@KhachID", KhachID);
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }
        public DataTable GetByFK_PhongID(int PhongID)
        {
            DataTable dt = new DataTable();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_GetByPhongId";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@PhongID", PhongID);
            sqlCmd.Connection.Open();

            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);
            sqlCmd.Connection.Close();
            return dt;
        }

        //-----Hàm Insert------
        public void Insert(M_Dangky M_Dangky)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Insert";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@DangkyID", SqlDbType.Int)).Value = M_Dangky.DangkyID;
            sqlCmd.Parameters.Add(new SqlParameter("@KhachID", SqlDbType.Int)).Value = M_Dangky.KhachID;
            sqlCmd.Parameters.Add(new SqlParameter("@PhongID", SqlDbType.Int)).Value = M_Dangky.PhongID;
            sqlCmd.Parameters.Add(new SqlParameter("@Giaphong", SqlDbType.Decimal)).Value = M_Dangky.Giaphong;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaydangky", SqlDbType.DateTime)).Value = M_Dangky.Ngaydangky;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaytraphong", SqlDbType.DateTime)).Value = M_Dangky.Ngaytraphong;
            sqlCmd.Parameters.Add(new SqlParameter("@Songay", SqlDbType.Int)).Value = M_Dangky.Songay;

            sqlCmd.Connection.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
        }

        //-----Hàm Update------
        public bool Update(M_Dangky M_Dangky)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Update";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@DangkyID", SqlDbType.Int)).Value = M_Dangky.DangkyID;
            sqlCmd.Parameters.Add(new SqlParameter("@KhachID", SqlDbType.Int)).Value = M_Dangky.KhachID;
            sqlCmd.Parameters.Add(new SqlParameter("@PhongID", SqlDbType.Int)).Value = M_Dangky.PhongID;
            sqlCmd.Parameters.Add(new SqlParameter("@Giaphong", SqlDbType.Decimal)).Value = M_Dangky.Giaphong;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaydangky", SqlDbType.DateTime)).Value = M_Dangky.Ngaydangky;
            sqlCmd.Parameters.Add(new SqlParameter("@Ngaytraphong", SqlDbType.DateTime)).Value = M_Dangky.Ngaytraphong;
            sqlCmd.Parameters.Add(new SqlParameter("@Songay", SqlDbType.Int)).Value = M_Dangky.Songay;

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }

        //-----Hàm xóa------
        public bool Delete(int DangkyID)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "Dangky_Delete";
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.Add(new SqlParameter("@DangkyID", DangkyID));

            sqlCmd.Connection.Open();
            int ketqua = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            return (ketqua > 0);
        }
    }
}
